﻿CREATE PROCEDURE [dbo].[usp_select_NonSubscribedAlertStatistics]	
AS
	SET NOCOUNT ON;

SELECT
	outerAlert.Name,
	(
		SELECT 
			COUNT(AlertSubscriptionID)

		FROM
			dbo.AlertSubscription sub
			INNER JOIN dbo.Alert alert on sub.AlertID = alert.AlertID

		WHERE
			alert.Name = outerAlert.Name
			AND sub.Active = 1
	) AS 'AlertSubscriptionCount',
	(
		SELECT 
			COUNT (AlertHistoryID)

		FROM
			dbo.AlertHistory

		WHERE
			DATEDIFF(hh, InsertedDate, GETUTCDATE()) < 1
			AND AlertName = outerAlert.Name
	) AS 'AlertCountLastHour',
	(
		SELECT 
			COUNT (AlertHistoryID)

		FROM
			dbo.AlertHistory

		WHERE
			DATEDIFF(dd, InsertedDate, GETUTCDATE()) < 1
			AND AlertName = outerAlert.Name
	) AS 'AlertCountLastDay',
	(
		SELECT 
			COUNT (AlertHistoryID)

		FROM
			dbo.AlertHistory

		WHERE
			DATEDIFF(mm, InsertedDate, GETUTCDATE()) < 1
			AND AlertName = outerAlert.Name
	) AS 'AlertCountLastMonth'

FROM
	dbo.Alert outerAlert
	LEFT JOIN dbo.AlertSubscription sub on outerAlert.AlertID = sub.AlertID

WHERE

	ISNULL(sub.Subscriber, '') != SYSTEM_USER

SET NOCOUNT OFF
